using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using interfaces;

namespace retrofit
{
    public class Query
    {
        /// <summary>
        /// the connection data member
        /// </summary>
        static OleDbConnection m_connection;


        /// <summary>
        /// SQL command data member
        /// </summary>
        static OleDbCommand m_command; 

        static string m_fileName = "data.mdb";  /// The constant file name of ACCESS DB
        private string m_path = "";
        static string m_oledb = "Provider=Microsoft.JET.OLEDB.4.0;data source = ";  /// OLE DB connection string.
        private string m_connWords = "";  /// sql connection sring


        /// <summary>
        /// Constructor, assign defaul database's path
        /// </summary>
        public Query()
        {
            m_connWords = m_oledb + m_path + m_fileName;    // setup connection word
        }


        /// <summary>
        /// setup connection word
        /// </summary>
        /// <param name="path">program installation path</param>
        public void setConnWords(string path)
        {
            m_path = path;
            m_connWords = m_oledb + m_path + m_fileName;    // setup connection word
        }

		
        /// <summary>
        /// connect to a database.
        /// this function initalize the m_connection variable by using default connection words m_connWords;
        /// </summary>
        public void connect()
        {
            m_connection = new OleDbConnection(m_connWords);
            m_connection.Open();

            m_command = m_connection.CreateCommand();
        }


        /// <summary>
        /// disconnect from a database.
        /// </summary>
        public void disconnect()
        {
            m_connection.Close();
        }


        /// <summary>
        /// query a given table with table name
        /// </summary>
		/// <param name="table">the table type we are going to query, access via enum Keyword.table_t</param>
		/// <param name="kw_idx">the input keyword index</param>
		/// <param name="out1">the output reference we are going to store query results</param>
		/// <param name="err_msgs">error message list</param>
		public void queryTable(Keyword.table_t table, string building_type, int[] kw_idx, Output out1, List<string> err_msgs)
        {
            // assign table name for query
            string tableName = Keyword.m_table[(int)table];

			// prepare keywords string.
			string condition = prepareKeywordString(table, building_type, kw_idx, err_msgs);

			queryRun(Keyword.m_energy_cat, tableName, condition, out1.m_energy, err_msgs);
        }


		/// <summary>
		/// Overloading function to return query output
		/// </summary>
		/// <param name="table">the table type we are going to query, access via enum Keyword.table_t</param>
		/// <param name="kw_idx">the input keyword index</param>
		/// <param name="err_msgs">error message list</param>
		/// <returns>query energy output</returns>
		public Output queryTable(Keyword.table_t table, string building_type, int[] kw_idx, List<string> err_msgs)
		{
			Output out1 = new Output();

			// assign table name for query
			string tableName = Keyword.m_table[(int)table];

			// prepare keywords string.
			string condition = prepareKeywordString(table, building_type, kw_idx, err_msgs);

			queryRun(Keyword.m_energy_cat, tableName, condition, out1.m_energy, err_msgs);

			return out1;
		}


		/// <summary>
		/// query a table with given keyword name and its instance ID, and get energy (electricity and gas) total cost data.
		/// </summary>
		/// <param name="kw_idx">the input keyword index</param>
		/// <param name="instance">the instance index</param>
		/// <param name="err_msgs">error message list</param>
		/// <returns>the output reference we are going to store query results</returns>
		public double[] queryTable(Keyword.keyword_t kw, int instance, List<string> err_msgs)
		{
			// assign table name for query
			string tableName = Energy.BEE_DefaultEnergyCostTableName;


            string condition = "Keyword = '" + Keyword.m_keyword[(int)kw] + "_" + instance + "'";
			double[] out1 = new double[(int)Keyword.energy_form_t.NUM_EFT];

			queryRun(Keyword.m_energy, tableName, condition, out1, err_msgs);

			return out1;
		}


		/// <summary>
		/// overloading function for query electricity rate based on climate condition
		/// </summary>
		/// <param name="climate">climate zone, access via interf.BEE_ClimateZone_T</param>
		/// <param name="rate">the output electricity rate</param>
		/// <param name="err_msgs">error message list</param>
		public void queryTable(interf.BEE_ClimateZone_T climate, double[] rate, List<string> err_msgs)
		{
			// sql where command
			string condition = interf.BEE_strAnalysisOptionType[(int)interf.BEE_AnalysisOption_T.ClimateZone_AOT] + " = " + (int)climate;

			queryRun(Energy.BEE_strElectricityRate, Energy.BEE_EnergyRateTableName, condition, rate, err_msgs);
		}


		/// <summary>
		/// overloading function to query measure cost.
		/// </summary>
		/// <param name="kw">the ECM keyword, access via Keyword.keyword_t</param>
		/// <param name="instance">the ECM instance integer</param>
		/// <param name="cost">measure object we are going to store data</param>
		/// <param name="err_msgs">error message list</param>
		public void queryTable(Keyword.keyword_t kw, int instance, double[] cost, List<string> err_msgs)
		{
			// sql where command
			string condition = "ID = " + instance;

			//Console.WriteLine(condition);

			string[] kw_str = new string[] { Keyword.m_keyword[(int)kw] };

			double[] temp = new double[1];

			// query material cost
			queryRun(kw_str, Cost.BEE_EcmCostCat[(int)Cost.BEE_EcmCostCat_T.Material_ECCT], condition, temp, err_msgs);
			cost[(int)Cost.BEE_EcmCostCat_T.Material_ECCT] = temp[0];

			//Console.WriteLine("Material: " + temp[0]);

			// query labor cost
			queryRun(kw_str, Cost.BEE_EcmCostCat[(int)Cost.BEE_EcmCostCat_T.Labor_ECCT], condition, temp, err_msgs);
			cost[(int)Cost.BEE_EcmCostCat_T.Labor_ECCT] = temp[0];
		}


		/// <summary>
		/// overloading function to query cost (material + labor hours) data. Two query are run (call queryRun() twice)
		/// </summary>
		/// <param name="err_msgs">error message list</param>
		public void queryMeasures(interf.BEE_Building_T building_type, List<DmMeasure> measure_list, Dictionary<string, int> dictionary_list, List<string> err_msgs)
		{
			string table_name = DmMeasure.MeasureTableName;
			string header_str = DmMeasure.MeasureTableHeaders[0];
			for (int i = 1; i < DmMeasure.MeasureTableHeaders.Length; ++i)
			{
				header_str += ", " + DmMeasure.MeasureTableHeaders[i];
			}
			
			//header_str = "Preprocess";
			m_command.CommandText = "SELECT " + header_str + " From " + table_name;

			//Console.WriteLine(m_command.CommandText);

			OleDbDataReader tableReader = m_command.ExecuteReader();

			bool found = false;         // flag to indicate whether result has been found from a query.

			while (tableReader.Read())
			{
				DmMeasure measure = new DmMeasure();

				//** read in each parameter one by one

				// sub-measure database ID
				measure.dm_id = Convert.ToInt32(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.ID_MPT]]);

				//sub-measure code
				measure.dm_code = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.SubMeasureCode_MPT]]);

				// sub-measure text
				measure.dm_text = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.MeasureText_MPT]]);

				// sub-measure's building type
				string str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.BuildingType_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_buildingType = new List<string>(str_tmp.Split(','));
				}

				// sub-measure's type
				measure.dm_measureType = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.MeasureType_MPT]]);

				// measure's option type
				measure.dm_optionType = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.OptionType_MPT]]);

				// measure's influence energy category type
				str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.InfluenceCategory_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_influEnergy = new List<string>(str_tmp.Split(','));
				}

				// measure's result table type
				measure.dm_resultTable = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.ResultTable_MPT]]);

				// measure's options list
				str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.Options_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_options = new List<string>(str_tmp.Split(','));
				}

				// measure's option effect factor list
				str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.Effects_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_effects = new List<string>(str_tmp.Split(','));
				}

				measure.dm_control = Convert.ToInt32(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.Control_MPT]]);

				measure.dm_lowCost = Convert.ToInt32(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.LowNoCost_MPT]]);

				// ** ------------------------------ cost data below ---------------------------------------- ** //

				// cost type
				measure.dm_costType = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.CostType_MPT]]);

				// cost applied area
				measure.dm_appliedArea = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.AppliedArea_MPT]]);

				// fix investment cost
				measure.dm_initiCost = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.InitialCost_MPT]]);
				
				// variable cost list
				str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.MaterialCost_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_materialCost = new List<string>(str_tmp.Split(','));
				}

				// measure's labor hour
				measure.dm_laborHour = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.LaborHour_MPT]]);

				// measure's O&M cost
				measure.dm_omCost = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.OMCost_MPT]]);

				// measure's slope for continuous cost data
//				measure.dm_costSlope = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.CostSlope_MPT]]);

				// ** ------------------------------ text and description below ----------------------------- ** //

				// measure's description
				measure.dm_description = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.Description_MPT]]);

				// measure's chinese description
				measure.dm_descriptionCN = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.DescriptionCN_MPT]]);

				// measure's chinese text
				measure.dm_textCN = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.MeasureTextCN_MPT]]);

				// measure's chinese option
				str_tmp = Convert.ToString(tableReader[DmMeasure.MeasureTableHeaders[(int)DmMeasure.measure_param_t.OptionsCN_MPT]]);
				if (str_tmp != "")
				{
					measure.dm_optionsCN = new List<string>(str_tmp.Split(','));
				}


				found = true;

				// ** handle building type according to selected building type input
				string build_type = interf.BEE_strBuilding_T[(int)building_type];
				// if the measure building type is a generic one or sepcific for selected building type
				// TODO, building type specific
				//if (measure.dm_buildingType == null || measure.dm_buildingType.Contains(build_type))
				{ 
					// store measure into measure list
					measure_list.Add(measure);
					dictionary_list.Add(measure.dm_code, measure.dm_id-1);	// we need index = object'sID - 1
				}

			}

			// give warning msg when query result can not be found.
			if (found == false)
			{
				err_msgs.Add("Error: Can not find information input(s) in database." + table_name + "!");
			}

			// close SQL reader but maintain connection.
			tableReader.Close();
		}


		public void queryMeasureGroups(interf.BEE_Building_T building_type, List<DmMeasureGroup> group_list, Dictionary<string, int> groupdictionary_list, List<string> err_msgs)
		{
			string table_name = DmMeasure.MeasureGroupTableName;
			string header_str = DmMeasure.MeasureGroupTableHeaders[0];
			for (int i = 1; i < DmMeasure.MeasureGroupTableHeaders.Length; ++i)
			{
				header_str += ", " + DmMeasure.MeasureGroupTableHeaders[i];
			}

			//header_str = "Preprocess";
			m_command.CommandText = "SELECT " + header_str + " From " + table_name;

			//Console.WriteLine(m_command.CommandText);

			OleDbDataReader tableReader = m_command.ExecuteReader();

			bool found = false;         // flag to indicate whether result has been found from a query.

			while (tableReader.Read())
			{
				DmMeasureGroup group = new DmMeasureGroup();

				//** read in each parameter one by one

				// measure group ID
				group.dm_id = Convert.ToInt32(tableReader[DmMeasureGroup.MeasureGroupTableHeaders[(int)DmMeasureGroup.measure_group_t.ID_MGT]]);

				//measure group code
				group.dm_code = Convert.ToString(tableReader[DmMeasureGroup.MeasureGroupTableHeaders[(int)DmMeasureGroup.measure_group_t.Code_MGT]]);

				// measure group name text
				group.dm_groupName = Convert.ToString(tableReader[DmMeasureGroup.MeasureGroupTableHeaders[(int)DmMeasureGroup.measure_group_t.MeasureGroup_MGT]]);

				// measure group name text (CN)
				group.dm_groupNameCN = Convert.ToString(tableReader[DmMeasureGroup.MeasureGroupTableHeaders[(int)DmMeasureGroup.measure_group_t.MeasureGroupCN_MGT]]);

				group_list.Add(group);
				groupdictionary_list.Add(group.dm_code, group.dm_id - 1);	// we need index = object'sID - 1

				found = true;
			}

			// give warning msg when query result can not be found.
			if (found == false)
			{
				err_msgs.Add("Error: Can not find information input(s) in database." + table_name + "!");
			}

			// close SQL reader but maintain connection.
			tableReader.Close();
		}

		/// <summary>
		/// basic query function to run user-defined query
		/// </summary>
		/// <param name="headers">the header array to keep the colomn name we are going to query</param>
		/// <param name="table_name">table string name</param>
		/// <param name="condition">the where condition we are going to query</param>
		/// <param name="out1">the output array, keep the same dimension as headers array</param>
		/// <param name="err_msgs">error messages list</param>
		private void queryRun(string[] headers, string table_name, string condition, double[] out1, List<string> err_msgs)
		{
			string header_str = headers[0];
			for (int i = 1; i < headers.Length; ++i)
			{
				header_str += ", " + headers[i];
			}

			m_command.CommandText = "SELECT " + header_str + " From " + table_name + " Where " + condition;

			//Console.WriteLine(m_command.CommandText);

			OleDbDataReader tableReader = m_command.ExecuteReader();

			bool found = false;         // flag to indicate whether result has been found from a query.
			while (tableReader.Read())
			{
				for (int i = 0; i < headers.Length; ++i)
				{
					out1[i] = Convert.ToDouble(tableReader[headers[i]]);
				}
				found = true;
			}

			// give warning msg when query result can not be found.
			if (found == false)
			{
				err_msgs.Add("Error: Can not find information input(s) in database." + table_name + "!");
			}

			// close SQL reader but maintain connection.
			tableReader.Close();
		}

		/// <summary>
		/// overloading function for query run. The function uses string list input.
		/// </summary>
		/// <param name="headers"></param>
		/// <param name="table_name"></param>
		/// <param name="condition"></param>
		/// <param name="out1"></param>
		/// <param name="err_msgs"></param>
		private void queryRun(List<string> headers, string table_name, string condition, double[] out1, List<string> err_msgs)
		{
			queryRun(headers.ToArray(), table_name, condition, out1, err_msgs);
		}

		/// <summary>
		/// prepare a query Where statement string
		/// </summary>
		/// <param name="table">the table type we are going prepare the query for</param>
		/// <param name="kw_idx">the selected keyword index from user inputs</param>		
		/// <param name="err_msgs">error message list</param>
		/// <returns>the condition string for SQL "where" statement</returns>
        private string prepareKeywordString(Keyword.table_t table, string building_type, int[] kw_idx, List<string> err_msgs)
        { 
            // keyword string added for SQL
//            List<Keyword.keyword_t> kws = new List<Keyword.keyword_t>();

			// we need to know builidng type first
			string kws_str = "BuildingType = \"" + building_type + "\"";

/*            switch(table)
            {
                // building envelope table keyword
                case Keyword.table_t.Envelope_TT:
                    kws.Add( Keyword.keyword_t.C1_KWT);
                    kws.Add( Keyword.keyword_t.C2_KWT);
                    kws.Add( Keyword.keyword_t.C3_KWT);
                    kws.Add( Keyword.keyword_t.C6_KWT);
                    kws.Add( Keyword.keyword_t.C7_KWT);
                    kws.Add( Keyword.keyword_t.C9_KWT);
                    break;

                // roof table keyword
                case Keyword.table_t .Roof_TT: 
                    kws.Add( Keyword.keyword_t.C4_KWT);
                    kws.Add( Keyword.keyword_t.C5_KWT);
                    break;

                // internal equipment table keyword
                case Keyword.table_t.IntEquipment_TT:
                    kws.Add( Keyword.keyword_t.H2_KWT);
                    break;
                
                // refrigeration table keyword
                case Keyword.table_t.Refrigeration_TT:
                    kws.Add( Keyword.keyword_t.F1_KWT);
                    break;

                // external lighting table keyword
                case Keyword.table_t.ExtLighting_TT:
                    kws.Add( Keyword.keyword_t.D4_KWT);
                    break;

                // internal lighting table keyword
                case Keyword.table_t.IntLight_TT:
                    kws.Add( Keyword.keyword_t.C6_KWT);
                    kws.Add( Keyword.keyword_t.C7_KWT);
                    kws.Add( Keyword.keyword_t.C8_KWT);
                    kws.Add( Keyword.keyword_t.D1_KWT);
                    kws.Add( Keyword.keyword_t.D2_KWT);
                    kws.Add( Keyword.keyword_t.D3_KWT);
                    kws.Add( Keyword.keyword_t.G1_KWT);
                    kws.Add( Keyword.keyword_t.G2_KWT);
                    break;

                // hvac table keyword
                case Keyword.table_t.Hvac_TT:
                    kws.Add( Keyword.keyword_t.E1_KWT);
                    kws.Add( Keyword.keyword_t.E2_KWT);
                    kws.Add( Keyword.keyword_t.E3_KWT);
                    kws.Add( Keyword.keyword_t.E4_KWT);
                    kws.Add( Keyword.keyword_t.E5_KWT);
                    kws.Add( Keyword.keyword_t.E6_KWT);
                    kws.Add( Keyword.keyword_t.E7_KWT);
                    kws.Add( Keyword.keyword_t.G3_KWT);
                    kws.Add( Keyword.keyword_t.G7_KWT);
                    break;

                // baseline table keyword
                case Keyword.table_t.Baseline_TT:
                    break;

                default:
                    err_msgs.Add("Error: unknown table type input.");
                    break;
            }
 */

            // baseline table
            if (table == Keyword.table_t.Baseline_TT)
            {
				kws_str = kws_str + " and " + "HasRefrigeration = " + kw_idx[0];
				return kws_str;
            }

			// for other empirical tables, we only need to query one record
			if (table == Keyword.table_t.IntLight_TT || table == Keyword.table_t.IntEquipment_TT || table == Keyword.table_t.ExtLighting_TT || table == Keyword.table_t.Refrigeration_TT)
			{
				kws_str = kws_str + " and " + "M" + Keyword.m_table_measure_str[(int)table][0] + " = 0";
				return kws_str;
			}

//            if (kws.Count <=0)
//				return kws_str;

            // put query condition into string.
			int idx = (int)Keyword.m_table_measure[(int)table][0];
			kws_str = kws_str + " and " + "M" + Keyword.m_keyword[idx] + " = " + kw_idx[idx].ToString();		// the 1st keyword, every measure starts with "M"
			for (int i = 1; i < (int)Keyword.m_table_measure[(int)table].Length; ++i)       // for the rest of keywords
			{
				idx = (int)Keyword.m_table_measure[(int)table][i];
				kws_str = kws_str + " and " + "M" + Keyword.m_keyword[idx] + " = " + kw_idx[idx].ToString();
			}
			return kws_str;
        }
    }
}